<?php
namespace app\sw\controller;

use app\api\controller\Base;
use app\sw\model\FileModel;
use app\sw\model\SheetFileUploadLogModel;
use app\sw\model\SheetModel;
use app\sw\model\SheetTemplateModel;
use app\sw\service\FileService;
use app\sw\service\SheetService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;

/**
 * Class Index
 * @package app\index\controller
 */
class Work extends Base
{


    // 创建某个月的工作表，这里通过某个年月，通过模板，更改模板中的变量。生成对应的模板变量
    public function createWorkSheet(){
        $year = input('year', date('Y'));
        $month = input('month', date('m'));
        
        try {
            SheetService::createWorkSheet($year, $month);
            //code...
        } catch (\Throwable $th) {
            return $this->returnError([], $th->getMessage());
        }
        return $this->returnSuccess([], '创建成功');
    
    }

    // 获取工作表列表，通过年月
    public function getWorkSheetList(){
        $this->checkUserLogin();
        $pageSize = input('pageSize', 20, 'intval');
        $pageNo = input('pageNo', 1, 'intval');

        $ym = input('ym', '', 'trim');

        $where = [];
        if($ym){
            $ym_arr = explode('-', $ym);
            if(count($ym_arr) == 2){
                $where['s_year'] = $ym_arr[0];
                $where['s_month'] = $ym_arr[1];
            }
        }

        if($this->checkIsAdmin() == false){
            $where[] = ['dept_id', 'in', [0, $this->user['dept_id']]];
        }

        $sheetList = $this->_query((new SheetModel())->where($where)->with(['template' => function($query){
            $query->with(['fields']);
        }, 'result_file', 'department']))->like('sheet_name', '', input())->equal('s_year', input())->equal('s_month', input())->equal('file_template_id', input())->equal('sheet_type', input())->order('s_year desc, s_month desc, id desc')->page($pageNo, false, false, $pageSize);    
        return $this->returnSuccess($sheetList, '获取成功');
    }

    // 获取工作表详情
    public function getWorkSheetDetail(){
        $this->checkUserLogin();
        $id = input('id', 0, 'intval');

        $data_map = [];

        if($this->checkIsAdmin() == false){
            $data_map['department_id'] = $this->user['dept_id'];
        }

        $sheetModel = SheetModel::where(['id' => $id])->with(['template' => function($query){
            $query->with(['department', 'fields', 'cloumn']);
        }, 'datas' => function($query) use ($data_map){
            $query->with(['department'])->where($data_map);
        }])->find();


        if(!$sheetModel){
            return $this->returnError([], '工作表不存在');
        }
        $sheetData = $sheetModel->toArray();

        // 这里要遍历数据模板中的cloumn，将数据表的中的month,next_month进行替换。
        if(isset($sheetData['template']['cloumn']) && $sheetData['template']['cloumn']){
            foreach ($sheetData['template']['cloumn'] as &$cloumn) {
                $cloumn['name'] = SheetService::replaceDefaultData($sheetData['s_year'], $sheetData['s_month'], $cloumn['name']);
            }
        }
        return $this->returnSuccess($sheetData, '获取成功');
    }


    public function saveSheetUploadFile(){
        $this->checkUserLogin();
        $file_id = input('file_id', 0, 'intval');
        $sheet_id = input('sheet_id', 0, 'intval');

        $sheetModel = SheetModel::where(['id' => $sheet_id])->find();
        if(!$sheetModel){
            return $this->returnError([], '工作表不存在');
        }

        $sheetModel->result_file_id = $file_id;
        $sheetModel->upload_time = time();
        $sheetModel->save();
        SheetFileUploadLogModel::create([
            'sheet_id' => $sheet_id,
            'file_id' => $file_id,
            'uid' => $this->user['id'],
        ]);
        return $this->returnSuccess([], '保存成功');
    }


    public function zipJiegouFileDownLoad(){

        // 这里打包下载

        // $year = input('year', date('Y'));
        // $month = input('month', date('m'));
        // $file_template_id = input('file_template_id', 0);

        $file_id = input('file_id', 0, 'intval');

        $fileModel = FileModel::where(['id' => $file_id])->find();

        // file_template_id = 1 
        $sheetList = $fileModel->sheets;

        if(!$sheetList){
            return $this->returnError([], '当月没有数据');
        }
        $excelFiles = [];
        foreach ($sheetList as $sheet) {
            // $file_path = $sheet['file_path'];
            // 判断是否已经上传过结果文件
            if($sheet['result_file_id']){
                $excelFiles[] = SITE_PATH . DS . 'public' . DS . $sheet['result_file']['xkey'];
                
            }else{
                $excelFiles[] = $sheet['file_path'];
                // return $this->returnError([], $sheet['sheet_name'] . '尚未上传上传结果文件');
            }
        }
        // 创建新的PHPExcel对象，这将是合并后的工作簿
        $mergedWorkbook = new \PHPExcel();
        // 假设您有一个包含文件路径的数组
        
        // 遍历每个文件
        foreach ($excelFiles as $file) {
            // 加载当前工作簿
            $objReader = \PHPExcel_IOFactory::createReaderForFile($file);
            $objPHPExcel = $objReader->load($file);

            // 遍历工作簿中的所有工作表
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                // 将工作表添加到合并后的工作簿中
                $mergedWorkbook->addExternalSheet($worksheet);
            }
        }

        // 删除合并后的工作簿中的第一个工作表
        $mergedWorkbook->removeSheetByIndex(0);

        // 设置保存的Excel格式
        $objWriter = \PHPExcel_IOFactory::createWriter($mergedWorkbook, 'Excel2007');
        $file_save_path = SITE_PATH . DS . 'public' . DS . 'upload' . DS . 'shangwu' . DS . date('Ym')  . DS . $fileModel['file_name'];

        $file_save_dir = dirname($file_save_path);
        // 临时存储文件。
        
        if(!is_dir($file_save_dir)){
            mkdir($file_save_dir, 0755, true);
        }
        
        // 保存合并后的工作簿
        $objWriter->save($file_save_path);

        $result = SheetService::saveFileRecord($file_save_path);


        return $this->returnSuccess($result, '打包成功');

    }


    // 保存sheet的data数据
    public function saveSheetData(){
        $this->checkUserLogin();
        $sheet_id = input('sheet_id', 0, 'intval');
        $datas = input('datas', '', 'trim');
        $datas = json_decode($datas, true);
        if(!$datas){
            return $this->returnError([], '数据格式错误');
        }
        $sheetModel = SheetModel::where(['id' => $sheet_id])->find();
        if(!$sheetModel){
            return $this->returnError([], '工作表不存在');
        }
        $sheetDataModel = new \app\sw\model\SheetDataModel();
        Db::startTrans();
        try {
            foreach ($datas as $data) {
                $rowModelInfo = $sheetDataModel->find($data['id']);
                // data1-data13判断数据有变化才进行修改
                $is_change = false;

                $saveData = [];

                for ($i=1; $i <= 13 ; $i++) { 
                    if($rowModelInfo['data' . $i] != $data['show_data' . $i]){
                        $saveData['data' . $i] = $data['show_data' . $i];
                        $is_change = true;
                    }
                }
                if($is_change){
                    $rowModelInfo->save($saveData);
                }
            }
            Db::commit();
        } catch (\Throwable $th) {
            //throw $th;
            Db::rollback();
        }
        $count = count($sheetModel->datas);
        $write_num = 0;
        // 通过判断 datas 填写的行数，判断填写进度
        foreach ($sheetModel->datas as $row) {
            // # code...
            foreach ($row->toArray() as $key => $value) {
                // # code...
                if(strpos($key, 'data') !== false && $value){
                    $write_num++;
                    break;
                }
            }
        }
        $sheetModel->progress = round(($write_num / $count) * 100, 2);
        $sheetModel->upload_time = time();
        $sheetModel->save();
        // 这里保存成功后，将数据保存到对应的excel中
        SheetService::saveSheetDataToExcel($sheetModel);
        return $this->returnSuccess([], '保存成功');
    }





}